package it.cinema.layar.biz;

import it.cinema.layar.Response;
import it.cinema.layar.data.ActionData;
import it.cinema.layar.data.GetParameter;
import it.cinema.layar.data.Hotspot;
import it.cinema.layar.data.HotspotData;
import it.cinema.layar.data.ObjectData;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.commons.httpclient.HttpClient;
import org.apache.commons.httpclient.HttpException;
import org.apache.commons.httpclient.methods.PostMethod;
import org.apache.commons.httpclient.methods.StringRequestEntity;
import org.apache.xmlbeans.XmlException;
import org.example.comunicationInputSchema.QueryDocument;
import org.example.comunicationInputSchema.ReccomendedCinemaDocument;
import org.example.comunicationInputSchema.QueryDocument.Query;
import org.example.comunicationInputSchema.QueryDocument.Query.Candidates;
import org.example.comunicationInputSchema.QueryDocument.Query.QueryParam;
import org.example.comunicationInputSchema.QueryDocument.Query.Targets;
import org.example.comunicationInputSchema.QueryDocument.Query.Candidates.Candidate;
import org.example.comunicationInputSchema.QueryDocument.Query.Targets.Target;
import org.example.comunicationInputSchema.ReccomendedCinemaDocument.ReccomendedCinema;
import org.example.comunicationInputSchema.ReccomendedCinemaDocument.ReccomendedCinema.Cinema;
import org.example.comunicationInputSchema.ReccomendedCinemaDocument.ReccomendedCinema.Cinema.Film;

public class RequestBiz {
	private static final Logger log = Logger.getLogger(RequestBiz.class
			.getName());

	private DataSource ds;
	private static final float kmDeg = 111.22625f;
	private static String layerName = "filmslocator";
	private static HashMap<String, String> generi = new HashMap<String, String>();
	
	static{
		generi.put("anim", "Animazione");
		generi.put("avve", "Avventura");
		generi.put("comm", "Commedia");
		generi.put("dram", "Drammatico");
		generi.put("fant", "Fantascienza");
		generi.put("azio", "Azione");
		generi.put("thri", "Thriller");
	}

	public RequestBiz() {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/dbcinema");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	public Response handleRequest(GetParameter gp) {
		Response response = new Response();
		List<HotspotData> hotspots = null;
		
		String errore = "Nessun POI trovato. Aggiustare i parametri di ricerca.";
		
		if(gp.isRicercaSemantica()){
			if(gp.getTitolo().isEmpty() & gp.getGenere().isEmpty() & gp.getSuggerimenti().isEmpty())
				errore = "Per la ricerca semantica necessario inserire almeno uno di questi tre parametri : Titolo, Genere o Keywords";
			else
				hotspots = ricercaSemantica(gp);
		}else{
			// Get all the active hotspots within the specified radius
			 hotspots = ricercaPuntuale(gp);
		}
		
		if (hotspots!=null && hotspots.size() > 0) {
			response.setHotspots(createAllHotspots(hotspots, gp.getLatitudine(), gp.getLongitudine()));
			response.setErrorCode(0);
			response.setErrorString("ok");
			response.setLayer(layerName);
			response.setRefreshDistance(10); // 10 meters
			response.setRefreshInterval(3600); // 180 seconds
		} else {
			response.setErrorCode(20);
			response.setErrorString(errore);
			response.setLayer(layerName);
			response.setRefreshDistance(10); // 10 meters
			response.setRefreshInterval(3600); // 180 seconds
		}
		return response;
	}

	public List<HotspotData> ricercaSemantica(GetParameter gp) {
		float delta[] = calculateDelta(gp.getRaggio() / 1000, gp.getLatitudine());
		float latmin = gp.getLatitudine()- delta[0];
		float latmax = gp.getLatitudine() + delta[0];
		float lonmin = gp.getLongitudine() - delta[1];
		float lonmax = gp.getLongitudine() + delta[1];
		List<HotspotData> hotspots = new ArrayList<HotspotData>();
		List<ActionData> ads = null;
		HotspotData hsd = null;
		ActionData ad = null;
		
		ResultSet resultSet = null;
		ResultSet resultSet1 = null;
		
		Connection connection = null;
		Statement statement = null;
		PreparedStatement ps = null;
		
		QueryDocument doc = QueryDocument.Factory.newInstance();
		Query root = doc.addNewQuery();
		QueryParam q = root.addNewQueryParam();
		q.setGenre(gp.getGenere());
		q.setTitle(gp.getTitolo());
		q.setKeywords(gp.getSuggerimenti());

		
		try {
			// Get Connection and Statement
			connection = ds.getConnection();

			recuperaFilmTarget(gp, root, connection);

			statement = connection.createStatement();
			
			String query = "select distinct f.idfilm, f.titolo, f.trama " +
					"from film f, cinema c, sala s, sala_proietta_film spf " +
					"where c.idcinema = s.cinema_idcinema and s.idsala = spf.sala_idsala and c.idcinema = spf.sala_cinema_idcinema and spf.film_idfilm = f.idfilm order by f.idfilm";
			resultSet = statement.executeQuery(query);

			String query1 = "select c.idcinema, c.nome from cinema c where c.idcinema in (select distinct spf.sala_cinema_idcinema from sala_proietta_film spf where spf.film_idfilm = ? )";
			ps = connection.prepareStatement(query1);			
			
			Candidates cs = root.addNewCandidates();
			Candidate c = null;
			org.example.comunicationInputSchema.QueryDocument.Query.Candidates.Candidate.Cinema cinema;			
			int idfilm;
			while (resultSet.next()) {
				c = cs.addNewCandidate();
				idfilm = new Integer(resultSet.getString("idfilm")).intValue();
				c.setIdfilm(idfilm);
				c.setTitle(resultSet.getString("titolo"));
				c.setStory(resultSet.getString("trama"));
				
				ps.setInt(1, idfilm);
				resultSet1 = ps.executeQuery();
				while(resultSet1.next()){
					cinema = c.addNewCinema();
					cinema.setIdcinema( new Integer(resultSet1.getString("idcinema")).intValue() );
					cinema.setStringValue(resultSet1.getString("nome"));
				}				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {if (null != resultSet)resultSet.close();} catch (SQLException e1) {e1.printStackTrace();}
			try {if (null != resultSet1)resultSet1.close();} catch (SQLException e11) {e11.printStackTrace();}
			try {if (null != statement)	statement.close();} catch (SQLException e2) {e2.printStackTrace();}
			try {if (null != ps) ps.close();} catch (SQLException e22) {e22.printStackTrace();}
		}
				
		PostMethod post = new PostMethod("http://localhost:8080/TestXMLBeans/TestXMLServlet");
        // Execute request
        try {	
    		post.setRequestEntity(new StringRequestEntity(doc.toString(),null,null));		
    		post.setRequestHeader("Content-type", "text/xml; charset=UTF-8");
            		
    		HttpClient httpclient = new HttpClient();
            
    		System.out.println("sending xml");

        	int result = httpclient.executeMethod(post);
            
            // Display status code
            //System.out.println("XML Sended with the follow Response status code: " + result);
            
            String xml = post.getResponseBodyAsString();

  		  	ReccomendedCinemaDocument rcd = ReccomendedCinemaDocument.Factory.parse(xml);		  
  		  	ReccomendedCinema root1=rcd.getReccomendedCinema();

			String query1 = "select * from cinema c where c.idcinema = ?";
			ps = connection.prepareStatement(query1);			

  		  	Cinema[] cs= root1.getCinemaArray();
			float lat = 0;
			float lon = 0;
			String idcinema;
 		  	for(Cinema c:cs){
 		  		
				ps.setInt(1, c.getIdcinema());
				resultSet = ps.executeQuery();
				while(resultSet.next()){
					lat = resultSet.getFloat("lat");
					lon = resultSet.getFloat("lon");
					if (lat >= latmin & lat <= latmax & lon >= lonmin & lon <= lonmax & c.getScore()>=0.1) {

						hsd = new HotspotData();
						idcinema = resultSet.getString("idcinema");
						hsd.setId(idcinema);
						hsd.setLat(resultSet.getFloat("lat"));
						hsd.setLon(resultSet.getFloat("lon"));
						
						hsd.setType(1);
						if(c.getScore() >= 0.1 & c.getScore()<0.4)
							hsd.setType(3); //rosso
						else if (c.getScore() > 0.3 & c.getScore() <0.7)
							hsd.setType(2); //giallo
							
						hsd.setTitle(resultSet.getString("nome"));
						hsd.setLine4(resultSet.getString("citta"));
						hsd.setLine3(resultSet.getString("indirizzo"));
						hsd.setLine2(resultSet.getString("zona"));
						hsd.setImageURL("http://nomami.dlinkddns.com/CinemaLayarServices/content/cinema.jpg");

						ads = new ArrayList<ActionData>();

						StringBuffer films = new StringBuffer();
						StringBuffer score = new StringBuffer();
		  		  		Film[] fs = c.getFilmArray();
		  		  		boolean first=true;
		  		  		for(Film f:fs){
		  		  			if(first){
		  		  				first = false;
		  		  				films.append(f.getIdfilm());
		  		  				score.append(f.getScore());
		  		  			}else{
		  		  				films.append(","+f.getIdfilm());
		  		  				score.append(","+f.getScore());
		  		  			}
		  		  		}

						ad = new ActionData();
						ad.setLabel("Films Shown");
						ad.setUri("http://nomami.dlinkddns.com/CinemaLayarMobile?cinema="+idcinema+"&films="+films+"&score="+score);
						ad.setContentType("text/html");
						ad.setActivityType(1);
						ads.add(ad);
						ad = new ActionData();
						ad.setLabel("Cinema Web Site");
						ad.setUri(resultSet.getString("sito"));
						ad.setContentType("text/html");
						ad.setActivityType(1);
						ads.add(ad);
						ad = new ActionData();
						ad.setLabel("Call Cinema");
						ad.setUri("tel:" + resultSet.getString("telefono"));
						ad.setContentType("application/vnd.layar.internal");
						ad.setActivityType(2);
						ads.add(ad);
						hsd.setActions(ads);
						hotspots.add(hsd);
					}					
				}  		  		
  		  	}		
            
        } catch (UnsupportedEncodingException e) {
    	   e.printStackTrace();
        } catch (HttpException e) {
    	   e.printStackTrace();
        } catch (IOException e) {
    	   e.printStackTrace();
        } catch (XmlException e) {
    	   e.printStackTrace();
        } catch (SQLException e) {
			e.printStackTrace();
		} finally {
            // Release current connection to the connection pool 
            // once you are done
            post.releaseConnection();
			try {if (null != resultSet)resultSet.close();} catch (SQLException e11) {e11.printStackTrace();}
			try {if (null != ps) ps.close();} catch (SQLException e22) {e22.printStackTrace();}
			try {if (null != connection)connection.close();} catch (SQLException e3) {e3.printStackTrace();}
       	}		
        
		return hotspots;
	}
	
	private void recuperaFilmTarget(GetParameter gp, Query q, Connection con){
		String query = createQueryTargets(gp);
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(query);

			int cont = 1;
			if(!gp.getAttore().isEmpty()){
				ps.setString(cont, "%"+gp.getAttore()+"%");
				cont++;
			}

			if(!gp.getTitolo().isEmpty()){
				ps.setString(cont, "%"+gp.getTitolo()+"%");
			}
			
			rs = ps.executeQuery();
			Targets ts = null;
			Target t = null;
			while(rs.next()){
				if(rs.isFirst())
					ts = q.addNewTargets();
				
				t = ts.addNewTarget();
				t.setIdfilm(rs.getInt("idfilm"));
				t.setTitle(rs.getString("titolo"));
				t.setStory(rs.getString("trama"));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{			
			try {if (null != rs)rs.close();} catch (SQLException e1) {e1.printStackTrace();}
			try {if (null != ps) ps.close();} catch (SQLException e22) {e22.printStackTrace();}
		}
	}
	
	public List<HotspotData> ricercaPuntuale(GetParameter gp) {

		float delta[] = calculateDelta(gp.getRaggio() / 1000, gp.getLatitudine());
		float latmin = gp.getLatitudine()- delta[0];
		float latmax = gp.getLatitudine() + delta[0];
		float lonmin = gp.getLongitudine() - delta[1];
		float lonmax = gp.getLongitudine() + delta[1];
		List<HotspotData> hotspots = new ArrayList<HotspotData>();
		List<ActionData> ads = null;

		ResultSet resultSet = null;
		Connection connection = null;
		PreparedStatement ps = null;
		HotspotData hsd = null;
		ObjectData od = null;
		ActionData ad = null;

		try {
			// Get Connection and Statement
			connection = ds.getConnection();

			String query = createQueryPuntuale(gp);
			ps = connection.prepareStatement(query);

			int cont = 1;
			if(!gp.getAttore().isEmpty()){
				ps.setString(cont, "%"+gp.getAttore()+"%");
				cont++;
			}

			if(!gp.getTitolo().isEmpty()){
				ps.setString(cont, "%"+gp.getTitolo()+"%");
			}
			
			resultSet = ps.executeQuery();

			float lat = 0;
			float lon = 0;
			String idcinema = "";
			while (resultSet.next()) {
				lat = resultSet.getFloat("lat");
				lon = resultSet.getFloat("lon");
				if (lat >= latmin & lat <= latmax & lon >= lonmin & lon <= lonmax) {

					hsd = new HotspotData();
					idcinema = resultSet.getString("idcinema");
					hsd.setId(idcinema);
					hsd.setLat(resultSet.getFloat("lat"));
					hsd.setLon(resultSet.getFloat("lon"));
					hsd.setTitle(resultSet.getString("nome"));
					hsd.setLine4(resultSet.getString("citta"));
					hsd.setLine3(resultSet.getString("indirizzo"));
					hsd.setLine2(resultSet.getString("zona"));
					hsd.setImageURL("http://nomami.dlinkddns.com/CinemaLayarServices/content/cinema.jpg");					
					
					ads = new ArrayList<ActionData>();

					StringBuffer films = new StringBuffer();
					films.append(resultSet.getString("idfilm"));
					while (resultSet.next()) {
						if (idcinema.equals(resultSet.getString("idcinema"))) {
							films.append("," + resultSet.getString("idfilm"));
						} else {
							resultSet.previous();
							break;
						}
					}
					if (resultSet.isAfterLast())
						resultSet.last();

					ad = new ActionData();
					ad.setLabel("Films Shown");
					ad.setUri("http://nomami.dlinkddns.com/CinemaLayarMobile?cinema="+idcinema+"&films="+films);
					ad.setContentType("text/html");
					ad.setActivityType(1);
					ads.add(ad);
					ad = new ActionData();
					ad.setLabel("Cinema Web Site");
					ad.setUri(resultSet.getString("sito"));
					ad.setContentType("text/html");
					ad.setActivityType(1);
					ads.add(ad);
					ad = new ActionData();
					ad.setLabel("Call Cinema");
					ad.setUri("tel:" + resultSet.getString("telefono"));
					ad.setContentType("application/vnd.layar.internal");
					ad.setActivityType(2);
					ads.add(ad);
					hsd.setActions(ads);
					hotspots.add(hsd);
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {if (null != resultSet)resultSet.close();} catch (SQLException e1) {e1.printStackTrace();}
			try {if (null != ps) ps.close();} catch (SQLException e2) {e2.printStackTrace();}
			try {if (null != connection)connection.close();} catch (SQLException e3) {e3.printStackTrace();}
		}

		return hotspots;
	}

	private float[] calculateDelta(int radius, float lat) {
		float[] delta = new float[2];
		delta[0] = radius / kmDeg; // delta Lat
		delta[1] = radius / (float) (kmDeg * Math.cos(Math.toRadians(lat))); // delta
																				// Lon
		return delta;
	}

	public List<Hotspot> createAllHotspots(List<HotspotData> hotspots,
			float currLat, float currLon) {
		List<Hotspot> results = new ArrayList<Hotspot>();
		for (HotspotData hsd : hotspots) {
			results.add(hsd.createHotSpot(currLat, currLon));
		}
		return results;
	}
	
	public String createQueryTargets(GetParameter gp){
		StringBuffer query = new StringBuffer();
		query.append("SELECT f.idfilm, f.titolo, f.trama from film f");
		boolean where = false;
		String and = "";

		if(!gp.getAttore().isEmpty()){
			query.append(", persona p, film_has_persona fhp WHERE (f.idfilm = fhp.film_idfilm AND p.idpersona = fhp.persona_idpersona AND p.nome_cognome LIKE ? )");
			and = " AND ";
			where = true;
		}
		
		if(!gp.getTitolo().isEmpty()){
			if(!where){
				query.append(" WHERE ");
				where = true;
			}
			query.append(and + "(f.titolo LIKE ?)");
			and = " AND ";
		}
		
		if(!gp.getGenere().isEmpty()){
			if(!where){
				query.append(" WHERE ");
				where = true;
			}
			String[] appo = gp.getGenere().split(",");
			query.append(and + "(");
			String or = "";
			for(int i=0; i<appo.length; i++){
				query.append(or+"f.genere ='"+ generi.get(appo[i])+ "'");
				or=" OR ";
			}			
			query.append(")");
		}
		
		return query.toString();
	}
	
	public String createQueryPuntuale(GetParameter gp){
		StringBuffer query = new StringBuffer();
		query.append("SELECT DISTINCT f.idfilm, c.idcinema, c.nome, c.lat, c.lon, c.indirizzo, c.citta, c.telefono, c.sito, c.zona FROM film f, cinema c, sala s, sala_proietta_film spf WHERE ");
		
		String and = "";

		if(!gp.getAttore().isEmpty() | !gp.getGenere().isEmpty() | !gp.getTitolo().isEmpty()){
			query.append(" f.idfilm in (SELECT f.idfilm FROM film f, persona p, film_has_persona fhp WHERE ");
//			query.append(" f.idfilm in (SELECT f.idfilm FROM film f WHERE ");
			
			if(!gp.getAttore().isEmpty()){
				query.append(and + "(f.idfilm = fhp.film_idfilm and p.idpersona = fhp.persona_idpersona and p.nome_cognome LIKE ?)");
				and = " AND ";
			}

			if(!gp.getTitolo().isEmpty()){
				query.append(and + "(f.titolo LIKE ?)");
				and = " AND ";
			}

			if(!gp.getGenere().isEmpty()){
				String[] appo = gp.getGenere().split(",");
				query.append(and + "(");
				String or = "";
				for(int i=0; i<appo.length; i++){
					query.append(or+"f.genere ='"+ generi.get(appo[i])+ "'");
					or=" OR ";
				}
				and = " AND ";
				query.append(")");
			}
			
			query.append(")");
		}
		
		query.append(and+"c.idcinema = s.cinema_idcinema AND s.idsala = spf.sala_idsala AND c.idcinema = spf.sala_cinema_idcinema AND spf.film_idfilm = f.idfilm order by c.idcinema");
				
		return query.toString();
	}
}
